﻿using Abp.Application.Services;
using Abp.Reflection.Extensions;
using Microsoft.EntityFrameworkCore;
using SportsPlatform.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using Dapper;

namespace SportsPlatform.SqlExecuter
{
    /// <summary>
    /// 执行命令仓储库
    /// </summary>
    [RemoteService(false)]  //隐藏自动生成的WebApi
    public class SqlExecuterRepository: ApplicationService ,ISqlExecuterRepository
    {
        #region 私有参数
        private readonly IConfiguration _appConfiguration;
        private readonly IDbConnection _dbConnection;
        #endregion

        #region 构造方法
        /// <summary>
        /// 构造方法
        /// </summary>
        public SqlExecuterRepository()
        {
            _appConfiguration = AppConfigurations.Get(typeof(SqlExecuterRepository).GetAssembly().GetDirectoryPathOrNull());
            _dbConnection = new SqlConnection(_appConfiguration["ConnectionStrings:Default"]);
        }
        #endregion

        #region 执行原生SQL语句方法

        /// <summary>
        /// 执行SQL返回IEnumerable
        /// </summary>
        /// <typeparam name="T">对象实体</typeparam>
        /// <param name="strSql">sql语句</param>
        /// <returns></returns>
        public IEnumerable<T> Query<T>(string strSql)
        {
            return _dbConnection.Query<T>(strSql).AsEnumerable();
        }

        /// <summary>
        /// 执行SQL返回IEnumerable
        /// </summary>
        /// <typeparam name="T">对象实体</typeparam>
        /// <param name="strSql">sql语句</param>
        /// <returns></returns>
        public virtual async Task<IEnumerable<T>> QueryAsync<T>(string strSql)
        {
            var query = await _dbConnection.QueryAsync<T>(strSql);
            return query.AsEnumerable();
        }

        /// <summary>
        /// 执行SQL返回IEnumerable
        /// </summary>
        /// <typeparam name="T">对象实体</typeparam>
        /// <param name="strSql">SQL语句</param>
        /// <param name="param">参数model</param>
        /// <returns></returns>
        public virtual IEnumerable<T> Query<T>(string strSql, object param)
        {
            return _dbConnection.Query<T>(strSql, param).AsEnumerable();
        }

        /// <summary>
        /// 执行SQL返回IEnumerable
        /// </summary>
        /// <typeparam name="T">对象实体</typeparam>
        /// <param name="strSql">SQL语句</param>
        /// <param name="param">参数model</param>
        /// <returns></returns>
        public virtual async Task<IEnumerable<T>> QueryAsync<T>(string strSql, object param)
        {
            var query = await _dbConnection.QueryAsync<T>(strSql, param);
            return query.AsEnumerable();
        }

        /// <summary>
        /// 执行SQL返回一个对象
        /// </summary>
        /// <typeparam name="T">对象实体</typeparam>
        /// <param name="strSql">SQL语句</param>
        /// <returns></returns>
        public virtual T QueryFirst<T>(string strSql)
        {
            return _dbConnection.QueryFirst<T>(strSql);
        }

        /// <summary>
        /// 执行SQL返回一个可为空的对象
        /// </summary>
        /// <typeparam name="T">对象实体</typeparam>
        /// <param name="strSql">SQL语句</param>
        /// <returns></returns>
        public virtual T QueryFirstOrDefault<T>(string strSql)
        {
            return _dbConnection.QueryFirstOrDefault<T>(strSql);
        }

        /// <summary>
        /// 执行SQL返回一个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public virtual async Task<T> QueryFirstAsync<T>(string strSql)
        {
            return await _dbConnection.QueryFirstAsync<T>(strSql);
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <param name="param">参数</param>
        /// <returns>1成功，0执行失败</returns>
        public virtual int Execute(string strSql, object param)
        {
            return _dbConnection.Execute(strSql, param) > 0 ? 1 : 0;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="strProcedure">过程名</param>
        /// <returns>1成功，0执行失败</returns>
        public virtual int ExecuteStoredProcedure(string strProcedure)
        {
            return _dbConnection.Execute(strProcedure, null, null, null, CommandType.StoredProcedure) == 0 ? 1 : 0;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="strProcedure">过程名</param>
        /// <param name="param">参数</param>
        /// <returns>1成功，0执行失败</returns>
        public virtual int ExecuteStoredProcedure(string strProcedure, object param)
        {
            return _dbConnection.Execute(strProcedure, param, null, null, CommandType.StoredProcedure) == 0 ? 1 : 0;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <param name="innerSql">SQL语句</param>
        /// <param name="page">页码</param>
        /// <param name="pageSize">当前页码中数据的数量</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public virtual IEnumerable<T> QueryByPage<T>(string innerSql, int page, int pageSize, object param)
        {
            var fromIndex = (page - 1) * pageSize + 1;
            var toIndex = page * pageSize;
            var sql = $"select * from ({innerSql}) tempx where tempx.RowIndex between {fromIndex} and {toIndex}";
            return _dbConnection.Query<T>(sql, param).AsEnumerable();
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <param name="innerSql">SQL语句</param>
        /// <param name="page">页码</param>
        /// <param name="pageSize">当前页码中数据的数量</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public virtual async Task<IEnumerable<T>> QueryByPageAsync<T>(string innerSql, int page, int pageSize, object param)
        {
            var fromIndex = (page - 1) * pageSize + 1;
            var toIndex = page * pageSize;
            var sql = $"select * from ({innerSql}) tempx where tempx.RowIndex between {fromIndex} and {toIndex}";
            var query = await _dbConnection.QueryAsync<T>(sql, param);
            return query.AsEnumerable();
        }

        /// <summary>
        /// 分页查询数据
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <param name="innerSql">SQL语句</param>
        /// <param name="skip">跳过数量</param>
        /// <param name="take">返回数量</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public virtual IEnumerable<T> QueryBySkip<T>(string innerSql, int skip, int take, object param)
        {
            var fromIndex = skip + 1;
            var toIndex = skip + take;
            var sql = $"select * from ({innerSql}) tempx where tempx.RowIndex between {fromIndex} and {toIndex}";
            return _dbConnection.Query<T>(sql, param).AsEnumerable();
        }

        /// <summary>
        /// 分页查询数据
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <param name="innerSql">SQL语句</param>
        /// <param name="skip">跳过数量</param>
        /// <param name="take">返回数量</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public virtual async Task<IEnumerable<T>> QueryBySkipAsync<T>(string innerSql, int skip, int take, object param)
        {
            var fromIndex = skip + 1;
            var toIndex = skip + take;
            var sql = $"select * from ({innerSql}) tempx where tempx.RowIndex between {fromIndex} and {toIndex}";
            var query = await _dbConnection.QueryAsync<T>(sql, param);
            return query.AsEnumerable();
        }
        #endregion
    }
}
